package DAO.MysqlDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DAO.InterfaceDAO.EtudiantDAO;
import DAO.InterfaceDAO.ProfesseurDAO;
import Factories.FactoryMySqlDAO;
import Model.Classe;
import Model.Etudiant;
import Model.Exercice;
import Model.Professeur;

public class ProfesseurDAO_Mysql implements ProfesseurDAO {

	private PreparedStatement psCreate, psRead, psUpdate, psDelete, psFind, psInvitEtud, psListClasse, psExist, psId, psEnabled,
	psListEtudiant, psListExercice;

	public ProfesseurDAO_Mysql(Connection cx) {

		try {
			psCreate = cx.prepareStatement("INSERT INTO professeur (login, password, nom, prenom, num_prof, active) VALUES(?, ?, ?, ?, ?, ?)");
			psRead   = cx.prepareStatement("SELECT * FROM professeur WHERE id = ?");
			psUpdate = cx.prepareStatement("UPDATE professeur SET login = ?," +
					" password = ? ," +
					" nom = ? ," +
					" prenom = ? ," +
					" num_prof = ? ," +
					" active = ? " +
			"  WHERE id = ? ");
			psDelete = cx.prepareStatement("DELETE FROM professeur WHERE id = ?");
			psFind   = cx.prepareStatement("SELECT * FROM professeur");
			psInvitEtud = cx.prepareStatement("INSERT INTO etudiant (nom, prenom, num_ine) VALUES (?, ? ,?)");
			psListClasse = cx.prepareStatement("SELECT * FROM classe WHERE professeur=?");
			psExist = cx.prepareStatement("SELECT count(*) as exist FROM professeur WHERE nom=? AND prenom=?");
			psId = cx.prepareStatement("SELECT id FROM professeur WHERE nom=? AND prenom=? ");
			psEnabled = cx.prepareStatement("SELECT active FROM professeur WHERE login=? AND password=?");
			psListEtudiant = cx.prepareStatement("SELECT * FROM professeur,etudiant WHERE professeur.id = ? AND etudiant.professeur = professeur.id");
			psListExercice = cx.prepareStatement("SELECT * FROM exercice WHERE auteur = ? AND theme = ?");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean create(Professeur p) {
		try {
			psCreate.setString(1, p.getLogin());
			psCreate.setString(2, p.getMotDePasse());
			psCreate.setString(3, p.getNom());
			psCreate.setString(4, p.getPrenom());
			psCreate.setString(5, p.getNumProf());
			psCreate.setBoolean(6, true); // p.isActive()); En attendant l'interface de l'administrateur
			psCreate.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}

	@Override
	public Professeur read(int id) {
		try {
			Professeur p = new Professeur();
			psRead.setInt(1, id);		
			ResultSet rs = psRead.executeQuery ();
			if(rs.next()) {
				p.setIdCompte(rs.getInt("id"));
				p.setLogin(rs.getString ("login"));
				p.setMotDePasse(rs.getString( "password"));
				p.setNom(rs.getString("nom"));
				p.setPrenom(rs.getString("prenom"));
				p.setNumProf(rs.getString("num_prof"));
				p.setActive(rs.getBoolean("active"));
			}

			return p;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public boolean update(Professeur p) {
		try {
			psUpdate.setString(1, p.getLogin());
			psUpdate.setString(2, p.getMotDePasse());
			psUpdate.setString(3, p.getNom());
			psUpdate.setString(4, p.getPrenom());
			psUpdate.setString(5, p.getNumProf());
			psUpdate.setBoolean(6, p.isActive());
			psUpdate.setInt(7, p.getIdCompte());
			psUpdate.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return true;
	}

	@Override
	public boolean delete(Professeur p) {
		try {
			psDelete.setInt(1, p.getIdCompte());
			psDelete.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}

	@Override
	public boolean exist(String nom, String prenom, String numProf) {
		try {
			psExist.setString(1, nom);
			psExist.setString(2, prenom);
			ResultSet rs = psExist.executeQuery();
			if(rs.next()) {
				return rs.getInt("exist")==1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

	@Override
	public int id(String nom, String prenom) {
		try {
			psId.setString(1, nom);
			psId.setString(2, prenom);
			ResultSet rs = psId.executeQuery();
			if(rs.next()){
				return rs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	@Override
	public void inviterEtud(Etudiant e) {
		try {
			psInvitEtud.setString(1, e.getNom());
			psInvitEtud.setString(2, e.getPrenom());
			psInvitEtud.setString(3, e.getNumIne());
			psInvitEtud.executeUpdate();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}

	@Override
	public boolean enabled(String login, String password) {
		try {
			psEnabled.setString(1, login);
			psEnabled.setString(2, password);
			ResultSet rs = psEnabled.executeQuery();
			if(rs.next()){
				return rs.getBoolean("active");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

	@Override
	public ArrayList<Etudiant> getEtudiants(int id) {
		ResultSet r2;
		try {
			psListEtudiant.setInt(1, id);

			r2 = psListEtudiant.executeQuery();
			ArrayList<Etudiant> etudiants = new ArrayList<Etudiant>();
			while(r2.next()) {
				int id2 = r2.getInt("etudiant.id");

				EtudiantDAO edao = FactoryMySqlDAO.getInstance().createEtudDAO();
				Etudiant courant = edao.read(id2);
				etudiants.add(courant);
			}

			return etudiants;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ArrayList<Classe> getClasses(int id) {
		try {
			psListClasse.setInt(1, id);

			ResultSet r = psListClasse.executeQuery();
			ArrayList<Classe> classes = new ArrayList<Classe>();
			while(r.next()) {
				classes.add(FactoryMySqlDAO.getInstance().createClasseDAO().read(r.getString("nom")));
			}
			return classes;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ArrayList<Exercice> getExercices(int idCompte, String theme) {
		try {
			psListExercice.setInt(1, idCompte);
			psListExercice.setString(2, theme);
			ResultSet rs = psListExercice.executeQuery();
			ArrayList<Exercice> exercices = new ArrayList<Exercice>();
			while(rs.next()) {
				exercices.add(FactoryMySqlDAO.getInstance().createExerciceDAO().read(rs.getInt("id")));
			}
			return exercices;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
